using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Xml;
using System.Xml.Linq;
using System.Linq;

namespace GuavaData
{

    public class Pivot : Transform
    {
        List<string> tables;                //name of i-th table
        List<string> attrColumns;           //name of attribute column for i-th table
        List<string> valColumns;            //name of value column for i-th table

        // Fields for run-time state
        List<Domain.Domain> tempLabels;     //domain of attribute column for i-th table
        List<Domain.Domain> tempDomains;    //domain of value column for i-th table
        Dictionary<string, List<string>> keyNames;             //non-A/V key column names for i-th table

        public static bool IsOracle = false;
        /// <summary>
        /// pivot operation
        /// </summary>
        public Pivot()
        {
            this.tables = new List<string>();
            this.attrColumns = new List<string>();
            this.valColumns = new List<string>();
            this.tempLabels = new List<GuavaData.Domain.Domain>();
            this.tempDomains = new List<GuavaData.Domain.Domain>();
        }

        /// <summary>
        /// pivot operation for a list of tables
        /// </summary>
        /// <param name="t">a list of tables to pivot</param>
        /// <param name="a">a list of attribute column names for each table</param>
        /// <param name="v">a list of value column names for each table</param>
        public Pivot(List<string> t, List<string> a, List<string> v)
        {
            if (t.Count != a.Count) throw new TransformException("The number of tables in a pivot operation must be the same as the number of attribute columns.");
            if (v.Count != a.Count) throw new TransformException("The number of value columns in a pivot operation must be the same as the number of attribute columns.");
            this.tables = t;
            this.attrColumns = a;
            this.valColumns = v;
            this.tempLabels = new List<GuavaData.Domain.Domain>(t.Count);
            this.tempDomains = new List<GuavaData.Domain.Domain>(t.Count);
        }

        /// <summary>
        /// add a table to the list of tables to pivot
        /// </summary>
        /// <param name="t">table name</param>
        /// <param name="a">attribute column name</param>
        /// <param name="v">value column name</param>
        public void Add(string t, string a, string v)
        {
            this.tables.Add(t);
            this.attrColumns.Add(a);
            this.valColumns.Add(v);
            this.tempLabels.Capacity = this.tempLabels.Capacity + 1;
            this.tempDomains.Capacity = this.tempDomains.Capacity + 1;
        }

        /// <summary>
        /// expand AddColumn statement
        /// </summary>
        /// <param name="ac">AddColumn object that add a column to the table</param>
        /// <param name="c">Command object corresponding to AddColumn statement</param>
        internal override void Expand(AddColumn ac, Command c)
        {
            if (!(this.tables.Contains(ac.Table)))
                return;
            // can't add Column to any table, throw error
            throw new DDLSideEffectException("Add Column: It's not allowed to add column " + ac.Column + " to table " + ac.Table + ".");
        }

        /// <summary>
        /// expand DropColumn statement
        /// </summary>
        /// <param name="dc">DropColumn object that drop a column from the table</param>
        /// <param name="c">Command object corresponding to DropColumn statement</param>
        internal override void Expand(DropColumn dc, Command c)
        {
            if (!(this.tables.Contains(dc.Table)))
                return;
            // can't drop any Column from a table, throw error
            throw new DDLSideEffectException("Drop Column: It's not allowed to drop column " + dc.Column + " from table " + dc.Table + ".");
        }

        /// <summary>
        /// expand AlterColumnRename statement
        /// </summary>
        /// <param name="acr">AlterColumnRename object that rename a column of the table</param>
        /// <param name="c">Command object corresponding to AlterColumnRename statement</param>
        internal override void Expand(AlterColumnRename acr, Command c)
        {
            // can only rename non-key columns, so nothing affected here.
        }


        /* 
         * <Add Element:>
         * 1) If the table matches and the new element is added to the Attribute column, 
         *    translate it into an Add Column statement.
         * 2) If the table matches and the new element is added to the Value column, 
         *    translate it into an Add Element statement for each of the pivoted columns (the ones whose name is drawn from the Attribute column).  
         * So, if there are 5 possible values in the domain of Attribute column and you see an Add Element statement come in for the Value column, 
         * it will turn into 5 Add Element statements.  So yes, the value column could be enumerable.
         */

        /// <summary>
        /// expand AddElement statement
        /// </summary>
        /// <param name="ae">AddElement object that add an element to the domain of one column of the table</param>
        /// <param name="c">Command object corresponding to AddElement statement</param>
        internal override void Expand(AddElement ae, Command c)
        {
            // If the table doesn't match, punt
            if (!(this.tables.Contains(ae.Table)))
                return;

            int i = this.tables.IndexOf(ae.Table);
            string t = this.tables[i];
            string a = this.attrColumns[i];
            string v = this.valColumns[i];
            Domain.Enumerated attrDomain = this.tempLabels[i] as Domain.Enumerated;
            Domain.Enumerated valDomain = this.tempDomains[i] as Domain.Enumerated;

            if ((attrDomain == null) && (valDomain == null))
                throw new Exception("Add Element: Domain of attribute/value column can't be casted to Domain.Enumerated.");

            if (ae.Column == a)
            {
                // If the domain contains the element, throw
                if (attrDomain.Items.Contains(ae.Element))
                    throw new Exception("Add Element to attribute column: Attribute Column of " + ae.Column + " already contains the element " + ae.Element + ".");

                if (this.keyNames[ae.Table].Contains(ae.Element))
                    throw new Exception("Add Element to attribute column: the element " + ae.Element + "that you are trying to add has duplicate name as one of key column that are not attribute column.");

                AddColumn acnew = new AddColumn(t, ae.Element, valDomain);
                c.NotifyChange(new Command(acnew));

                //Alter the Domain
                Domain.Enumerated enew = new GuavaData.Domain.Enumerated(attrDomain.Items);
                enew.Items.Add(ae.Element);
                this.tempLabels[i] = enew;
            }

            if (ae.Column == v)
            {
                // If the domain contains the element, throw
                if (valDomain.Items.Contains(ae.Element))
                    throw new Exception("Add Element to value column: Value Column of " + ae.Column + " already contains the element " + ae.Element + ".");

                List<Command> cnew = new List<Command>();

                foreach (string s in attrDomain.Items)
                {
                    AddElement aenew = new AddElement(t, s, ae.Element);
                    cnew.Add(new Command(aenew));
                }

                c.NotifyChange(cnew.ToArray());

                //Alter the Domain
                Domain.Enumerated enew = new GuavaData.Domain.Enumerated(valDomain.Items);
                enew.Items.Add(ae.Element);
                this.tempDomains[i] = enew;
            }
        }


        /* 
         * Rename Element:
         * can happen to any column.  
         * if it happens on a key column that isn't the Attribute column, it's unaffected.
         */

        /// <summary>
        /// expand AlterElementRename statement
        /// </summary>
        /// <param name="aer">AlterElementRename object that rename an element in the domain of one column of the table</param>
        /// <param name="c">Command object corresponding to AlterElementRename statement</param>
        internal override void Expand(AlterElementRename aer, Command c)
        {
            // If the table doesn't match, punt
            if (!(this.tables.Contains(aer.Table)))
                return;

            int i = this.tables.IndexOf(aer.Table);
            string t = this.tables[i];
            string a = this.attrColumns[i];
            string v = this.valColumns[i];
            Domain.Enumerated attrDomain = this.tempLabels[i] as Domain.Enumerated;
            Domain.Enumerated valDomain = this.tempDomains[i] as Domain.Enumerated;

            if (attrDomain == null && valDomain == null)
                throw new Exception("Rename Element: Domain of attribute/value column can't be casted to Enumerated.");

            if (aer.Column == a)
            {
                // If the domain doesn't contain the element, throw
                if (!attrDomain.Items.Contains(aer.OldName))
                    throw new Exception("Rename Element in Attribute Column: Attribute Column of " + aer.Column + " doesn't contain the old element " + aer.OldName + ".");
                // if the new element name is already in the domain, throw
                if (attrDomain.Items.Contains(aer.NewName))
                    throw new Exception("Rename Element in Attribute Column: Element name can't be renamed to" + aer.NewName + " because it already existed.");
                if (this.keyNames[aer.Table].Contains(aer.NewName))
                    throw new Exception("Rename Element in Attribute Column: the new element name " + aer.NewName + "is duplicate as one of key columns that are not attribute column.");

                AlterColumnRename acrnew = new AlterColumnRename(t, aer.OldName, aer.NewName);
                c.NotifyChange(new Command(acrnew));

                //Alter the Domain
                Domain.Enumerated enew = new GuavaData.Domain.Enumerated(attrDomain.Items);
                enew.Items.Remove(aer.OldName);
                enew.Items.Add(aer.NewName);
                this.tempLabels[i] = enew;
            }

            if (aer.Column == v)
            {
                // If the domain doesn't contain the element, throw
                if (!valDomain.Items.Contains(aer.OldName))
                    throw new Exception("Rename Element in Value Column: Value Column of " + aer.Column + " doesn't contain the old element " + aer.OldName + ".");
                // if the new element name is already in the domain, throw
                if (valDomain.Items.Contains(aer.NewName))
                    throw new Exception("Rename Element in Value Column: Element name can't be renamed to" + aer.NewName + " because it already existed.");

                List<Command> cnew = new List<Command>();

                foreach (string s in attrDomain.Items)
                {
                    AlterElementRename aernew = new AlterElementRename(t, s, aer.OldName, aer.NewName);
                    cnew.Add(new Command(aernew));
                }

                c.NotifyChange(cnew.ToArray());

                //Alter the Domain
                Domain.Enumerated enew = new GuavaData.Domain.Enumerated(valDomain.Items);
                enew.Items.Remove(aer.OldName);
                enew.Items.Add(aer.NewName);
                this.tempDomains[i] = enew;
            }
        }

        /// <summary>
        /// expand DropElement statement
        /// </summary>
        /// <param name="de">DropElement object that drop an element from the domain of one column of the table</param>
        /// <param name="c">Command object corresponding to DropElement statement</param>
        internal override void Expand(DropElement de, Command c)
        {
            // If the table doesn't match, punt
            if (!(this.tables.Contains(de.Table)))
                return;

            int i = this.tables.IndexOf(de.Table);
            string t = this.tables[i];
            string a = this.attrColumns[i];
            string v = this.valColumns[i];
            Domain.Enumerated attrDomain = this.tempLabels[i] as Domain.Enumerated;
            Domain.Enumerated valDomain = this.tempDomains[i] as Domain.Enumerated;

            if (attrDomain == null && valDomain == null)
                throw new Exception("Drop Element: Domain of attribute/value column can't be casted to Enumerated.");

            if (de.Column == a)
            {
                // If the domain doesn't contain the element, throw
                if (!attrDomain.Items.Contains(de.Element))
                    throw new Exception("Drop Element in attribute column: Column" + de.Column + " doesn't contain the element " + de.Element + ".");

                DropColumn dcnew = new DropColumn(t, de.Element);
                c.NotifyChange(new Command(dcnew));

                //Alter the Domain
                Domain.Enumerated enew = new GuavaData.Domain.Enumerated(attrDomain.Items);
                enew.Items.Remove(de.Element);
                this.tempLabels[i] = enew;

            }
            if (de.Column == v)
            {
                // If the domain doesn't contain the element, throw
                if (valDomain.Items.Contains(de.Element))
                    throw new Exception("Drop element in value column: Column" + de.Column + " doesn't contain the element " + de.Element + ".");

                List<Command> cnew = new List<Command>();

                foreach (string s in attrDomain.Items)
                {
                    DropElement denew = new DropElement(t, s, de.Element);
                    cnew.Add(new Command(denew));
                }

                c.NotifyChange(cnew.ToArray());

                //Alter the Domain
                Domain.Enumerated enew = new GuavaData.Domain.Enumerated(valDomain.Items);
                enew.Items.Remove(de.Element);
                this.tempDomains[i] = enew;
            }
        }

        /// <summary>
        /// expand AddTable statement
        /// </summary>
        /// <param name="at">AddTable oject that add a table</param>
        /// <param name="c">Command object corresponding to AddTable statement</param>
        internal override void Expand(AddTable at, Command c)
        {
            // If the table doesn't match, punt
            if (!(this.tables.Contains(at.Table)))
                return;

            int i = this.tables.IndexOf(at.Table);
            string t = this.tables[i];
            string a = this.attrColumns[i];
            string v = this.valColumns[i];

            Domain.Enumerated attrDomain = null;
            Domain.Domain valDomain = null;

            // find the attribute column in the list of at.Columns
            int j = at.Columns.IndexOf(a);
            if (j < 0)
                throw new Exception("Add Table: Attribute column " + a + " of table " + at.Table + "was not found.");
            attrDomain = at.Domains[j] as Domain.Enumerated;
            this.tempLabels.Insert(i, attrDomain);

            //find the value column in the list of at.Columns
            int k = at.Columns.IndexOf(v);
            if (k < 0)
                throw new Exception("Add Table: Value column " + v + " of table " + at.Table + "was not found.");
            valDomain = at.Domains[k];
            this.tempDomains.Insert(i, valDomain);

            if (attrDomain == null || valDomain == null)
                throw new Exception("Add Table: attribute column can't be cast to Enumerated.");

            //for each table to pivot, remember those columns that are that are NOT the A/V column 
            this.keyNames = new Dictionary<string, List<string>>();
            this.keyNames[at.Table] = new List<string>(at.Keys);  //remember all key columns
            this.keyNames[at.Table].Remove(a);  //remove Attribute column

            // pivoted table columns
            List<string> colNew = new List<string>(at.Columns);
            colNew.Remove(a);  //remove attribute column
            colNew.Remove(v);  //remove value column
            foreach (string s in attrDomain.Items)    //foreach element in attr column, add to the column of pivoted table
                colNew.Add(s);

            //pivoted table domains
            List<Domain.Domain> domNew = new List<GuavaData.Domain.Domain>(at.Domains);
            domNew.Remove(this.tempLabels[i]);    //this.tempLabels[i] - attribute domain
            domNew.Remove(this.tempDomains[i]);   //this.tempDomains[i] - value domain
            foreach (string s in attrDomain.Items)    //foreach new column in pivoted table, add the domain for it.
                domNew.Add(this.tempDomains[i]);

            //pivoted table keys
            List<string> kNew = new List<string>(at.Keys);
            kNew.Remove(a);

            AddTable atNew = new AddTable(at.Table, colNew, domNew, kNew);
            c.NotifyChange(new Command(atNew));
        }

        /// <summary>
        /// expand DropTable statement
        /// </summary>
        /// <param name="dt">DropTable object that drop a table</param>
        /// <param name="c">Command object corresponding to DropTable statement</param>
        internal override void Expand(DropTable dt, Command c)
        {
            // If the table doesn't match, punt
            if (!(this.tables.Contains(dt.Table)))
                return;

            int i = this.tables.IndexOf(dt.Table);
            //string t = this.tables[i];
            //string a = this.attrColumns[i];
            //string v = this.valColumns[i];
            Domain.Enumerated attrDomain = this.tempLabels[i] as Domain.Enumerated;
            Domain.Enumerated valDomain = this.tempDomains[i] as Domain.Enumerated;

            if (attrDomain == null && valDomain == null)
                throw new Exception("Drop Table: Table " + dt.Table + " doesn't exist at all.");

            //null out attribute/value domain in memory.
            this.tempLabels[i] = null;
            this.tempLabels[i] = null;
        }

        /*
         * can only update non-key column - V
         * condition can only be equality condition on key columns - K/A
         * two cases:
         * * 1) condition column includes A 
         * * 2) condition column does not include A
         */

        /// <summary>
        /// expand Update statement
        /// </summary>
        /// <param name="u">Update object that update some rows of the table</param>
        /// <param name="c">Command object corresponding to Update statement</param>
        internal override void Expand(Update u, Command c)
        {
            // If the table doesn't match, punt
            if (!(this.tables.Contains(u.Table)))
                return;

            int i = this.tables.IndexOf(u.Table);
            string t = this.tables[i];
            string a = this.attrColumns[i];
            string v = this.valColumns[i];
            Domain.Enumerated attrDomain = this.tempLabels[i] as Domain.Enumerated;
            Domain.Enumerated valDomain = this.tempDomains[i] as Domain.Enumerated;

            // Process the child
            u.Child.Visit(this, c);

            List<string> upCol = new List<string>();   //columns to be updated - originally only V column
            List<string> newCondCol = new List<string>(u.CondColumns);    //condition columns - key columns
            List<Condition> newCond = new List<Condition>(u.Conditions);     //conditions - equality conditions

            /*
             * <an example here>
             * old table: (K, A, V) with attribute domain of (X, Y, Z, W)
             * new table: (K, X, Y, Z, W)
             * 
             * 1)UPDATE T SET V=0 WHERE A=X; ==> UPDATE T SET X=0; --condition on A
             * 2)UPDATE T SET V=0; ==> UPDATE T SET X=0; --condition not on A
             *                     ==> UPDATE T SET Y=0;
             *                     ==> UPDATE T SET Z=0;
             *                     ==> UPDATE T SET W=0;
             */
            if (u.CondColumns.Contains(a))
            {
                int j = u.CondColumns.IndexOf(a);

                newCondCol.Remove(a);
                newCond.Remove(u.Conditions[j]);

                upCol.Add(u.Conditions[j].Args[0].ToString());

                Update unew = new Update(u.Table, newCondCol, newCond, upCol, u.Child);
                c.NotifyChange(c.CloneState(unew));
            }
            else
            {
                List<Command> cnew = new List<Command>();
                foreach (string s in attrDomain.Items)
                {
                    upCol.Add(s);
                    cnew.Add(c.CloneState(new Update(u.Table, u.CondColumns, u.Conditions, upCol, u.Child)));
                    upCol.Remove(s);
                }
                c.NotifyChange(cnew.ToArray());
            }
        }

        /// <summary>
        /// expand Insert statement
        /// </summary>
        /// <param name="i">Insert object that insert rows to the table</param>
        /// <param name="c">Command object corresponding to Insert statement</param>
        internal override void Expand(Insert i, Command c)
        {
            // If the table doesn't match, punt
            if (!(this.tables.Contains(i.Table)))
                return;

            int j = this.tables.IndexOf(i.Table);
            string t = this.tables[j];
            string a = this.attrColumns[j];
            string v = this.valColumns[j];
            Domain.Enumerated attrDomain = this.tempLabels[j] as Domain.Enumerated;
            Domain.Enumerated valDomain = this.tempDomains[j] as Domain.Enumerated;

            List<string> notA_Keys = keyNames[i.Table];  //keys[i.Table] - Attribute column

            // Process the child
            i.Child.Visit(this, c);

            if (i.Child is RowConstant)
            {
                RowConstant rc = i.Child as RowConstant;

                //get keys of the row to be inserted
                RowConstant rckey = new RowConstant();
                foreach (string s in notA_Keys)
                {
                    int index = rc.exposedColumns.IndexOf(s);
                    rckey.AddValue(s, rc.exposedDomains[index], rc.Fields[index]);
                }

                string talias = c.nextTableAlias(i.Table);
                List<Domain.Domain> keyDoms = new List<Domain.Domain>();
                for (int ind = 0; ind < notA_Keys.Count; ind++)
                {
                    keyDoms.Add(new Domain.UniqueIdentifier());
                }

                TableRef tr = new TableRef(i.Table, talias, notA_Keys.Concat(new List<string> { a, v }),
                    notA_Keys.Concat(new List<string> { a, v }), keyDoms.Concat(new List<Domain.Domain> { attrDomain, valDomain }));
                Project p2 = new Project(tr, notA_Keys);    //get keys that already existed
                Except except = new Except(rckey, p2);
                Insert insert = new Insert(i.Table, notA_Keys, except); //insert the keys that didn't exist

                //update the row to be inserted, create update operation based on new schema
                List<string> upCol = new List<string>();             //columns to be updated - originally only V column
                List<Condition> newCond = new List<Condition>();     //conditions - equality conditions

                int aIndex = rc.exposedColumns.IndexOf(a);
                upCol.Add(rc.Fields[aIndex].ToString());

                foreach (string s in notA_Keys)
                {
                    int index = rc.exposedColumns.IndexOf(s);
                    newCond.Add(Condition.NewCondition(ConditionType.EQ, rc.Fields[index])); //keys value
                }

                //get the value of column to be updated
                int k = rc.exposedColumns.IndexOf(v);
                RowConstant rcv = new RowConstant();
                rcv.AddValue(v, rc.exposedDomains[k], rc.Fields[k]);

                Update update = new Update(i.Table, notA_Keys, newCond, upCol, rcv);

                c.NotifyChange(new Command(insert), new Command(update));
            }
            else
            {
                //get the result of query
                Transaction trans = new Transaction(i.Child);
                DataSet ds = this.Process(trans);
                if (!ds.Tables.Contains(i.Table))
                    throw new Exception("Insert: Query did not return table " + i.Table);

                DataTable dt = ds.Tables[i.Table];

                //insert part
                Project p1 = new Project(i.Child, notA_Keys);   //get keys that already existed

                string talias = c.nextTableAlias(i.Table);
                TableRef tr = new TableRef(i.Table, talias);
                Project p2 = new Project(tr, notA_Keys);        //get keys of rows to be inserted

                Insert insert = new Insert(i.Table, notA_Keys, new Except(p1, p2)); //insert the keys that didn't exist

                List<Command> clist = new List<Command>();
                clist.Add(new Command(insert));

                //update the rows to be inserted, create update operation based on new schema
                foreach (DataRow dr in dt.Rows)
                {
                    RowConstant rc = new RowConstant();
                    List<string> newCol = new List<string>();            //column to be updated - originally only V column
                    List<string> newCondCol = new List<string>();        //condition columns - key columns
                    List<Condition> newCond = new List<Condition>();     //conditions - equality conditions

                    foreach (DataColumn dc in dt.Columns)
                    {
                        if (notA_Keys.Contains(dc.ColumnName))
                        {
                            newCondCol.Add(dc.ColumnName);   //keys column where to update
                            newCond.Add(Condition.NewCondition(ConditionType.EQ, dr[dc.ColumnName])); //keys value
                        }

                        if (dc.ColumnName == a)
                            newCol.Add(dr[a].ToString());  //column to be updated

                        if (dc.ColumnName == v)
                            rc.AddValue(v, valDomain, dr[v]);  //value as a RowConstant of the column to be updated
                    }

                    Update update = new Update(i.Table, newCondCol, newCond, newCol, rc);
                    clist.Add(new Command(update));
                }
                c.NotifyChange(clist.ToArray());
            }
        }

        //The table containing the foreign key is called the child table

        /// <summary>
        /// expand ForeignKey statement
        /// </summary>
        /// <param name="fk">ForeignKey object</param>
        /// <param name="c">Command object corresponding to ForeignKey statement</param>
        internal override void Expand(ForeignKey fk, Command c)
        {
            if (!(this.tables.Contains(fk.ChildTable)) || !(this.tables.Contains(fk.ParentTable)))
                return;

            if (this.tables.Contains(fk.ChildTable))
            {
                int jChild = this.tables.IndexOf(fk.ChildTable);
                string tChild = this.tables[jChild];
                string aChild = this.attrColumns[jChild];
                string vChild = this.valColumns[jChild];
                Domain.Enumerated attrDomain_Child = this.tempLabels[jChild] as Domain.Enumerated;
                //Domain.Enumerated valDomain_Child = this.tempDomains[jChild] as Domain.Enumerated;

                // fk on attribute column of child table
                if (fk.ChildColumns.Contains(aChild))
                {
                    fk.ChildColumns.Remove(aChild);
                    fk.ParentColumns.Remove(aChild);
                }
                if (fk.ChildConditionColumns.Contains(aChild))
                {
                    int j = fk.ChildConditionColumns.IndexOf(aChild);
                    Condition cond = fk.ChildConditions[j];

                    fk.ChildConditionColumns.Remove(aChild);
                    fk.ChildConditions.RemoveAt(j);

                    if (fk.ChildColumns.Contains(vChild))
                    {
                        int i = fk.ChildColumns.IndexOf(vChild);
                        fk.ChildColumns.Remove(vChild);
                        fk.ChildColumns.Add(cond.Args[0].ToString());
                    }
                    else
                    {
                        c.NotifyChange();
                    }
                }
                else if (!fk.ChildConditionColumns.Contains(aChild))
                {
                    if (fk.ChildColumns.Contains(vChild))
                    {
                        List<Command> newc = new List<Command>();
                        List<string> fkCol = new List<string>(fk.ChildColumns);

                        fkCol.Remove(vChild);

                        foreach (string s in attrDomain_Child.Items)
                        {
                            fkCol.Add(s);
                            ForeignKey fknew = new ForeignKey(fk.ChildTable, fk.ChildConditionColumns, fk.ChildConditions, fkCol,
                                                              fk.ParentTable, fk.ParentConditionColumns, fk.ParentConditions, fk.ParentColumns);
                            newc.Add(new Command(fknew));
                            fkCol.Remove(s);
                        }

                        c.NotifyChange(newc.ToArray());
                    }
                }

            }
            if (this.tables.Contains(fk.ParentTable))
            {
                int jParent = this.tables.IndexOf(fk.ParentTable);
                string tParent = this.tables[jParent];
                string aParent = this.attrColumns[jParent];
                string vParent = this.valColumns[jParent];
                Domain.Enumerated attrDomain_Parent = this.tempLabels[jParent] as Domain.Enumerated;
                //Domain.Enumerated valDomain_Parent = this.tempDomains[jParent] as Domain.Enumerated;

                // fk on attribute column of child table
                if (fk.ParentColumns.Contains(aParent))
                {
                    fk.ParentColumns.Remove(aParent);
                    fk.ChildColumns.Remove(aParent);
                }
            }
        }

        /// <summary>
        /// expand TableRef statement
        /// </summary>
        /// <param name="tr">TableRef object that reference the table</param>
        /// <param name="c">Command object corresponding to TableRef statement</param>
        internal override void Expand(TableRef tr, Command c)
        {
            if (!(this.tables.Contains(tr.Table)))
                return;
            List<string> oldColumns = tr.exposedColumns;

            int j = this.tables.IndexOf(tr.Table);
            string t = this.tables[j];
            string a = this.attrColumns[j];
            string v = this.valColumns[j];
            Domain.Enumerated attrDomain = this.tempLabels[j] as Domain.Enumerated;
            Domain.Enumerated valDomain = this.tempDomains[j] as Domain.Enumerated;

            List<string> keys = keyNames[tr.Table];

            TableRef tref = new TableRef(tr.Table, tr.Alias);
            string t_alias = c.nextTableAlias(tref.Table);

            //keys column
            foreach (string s in keys)
            {
                int index = tr.Columns.IndexOf(s);
                tref.AddColumn(s, tr.exposedColumns[index], tr.exposedDomains[index]);
            }

            //other columns coming from attribute column domain
            foreach (string s in attrDomain.Items)
            {
                if (IsOracle)
                    tref.AddColumn(s, s, valDomain);
                else
                {
                    string s_alias = t_alias + "_" + s;
                    tref.AddColumn(s, s_alias, valDomain);
                }
            }

            //unpivot
            UnpivotOp unpivot = new UnpivotOp(tref, oldColumns, a, v, tr.Alias);

            if (tr.parent != null)
                tr.parent.NotifyChange(tr, unpivot);
        }

        public override Transform Inverse()
        {
            return new Unpivot(tables, attrColumns, valColumns);
        }

        public override List<XElement> ToXElement()
        {
            XElement x = new XElement("Transform", new XAttribute("type", "Pivot"));
            x.Add(new XElement("tables"));
            x.Add(new XElement("attributes"));
            x.Add(new XElement("values"));
            for (int i = 0; i < tables.Count; i++)
            {
                String prefix = "";
                int oot;
                if (int.TryParse(tables[i].Substring(0, 1), out oot)) prefix = "c_";
                x.Element("tables").Add(new XElement(prefix + tables[i]));
                x.Element("attributes").Add(new XElement(attrColumns[i]));
                x.Element("values").Add(new XElement(valColumns[i]));
            }
            List<XElement> xList = new List<XElement>();
            if (!(this.Child is SqlProvider))
                xList = ((Transform)this.Child).ToXElement();
            xList.Add(x);
            return xList;
        }

        new public static Pivot FromXElement(XElement x)
        {
            List<string> tables = new List<string>();
            List<string> attributes = new List<string>();
            List<string> values = new List<string>();

            foreach (var xe in x.Elements())
            {
                switch (xe.Name.LocalName)
                {
                    case "tables":
                        foreach (XElement tableElement in xe.Elements())
                        {
                            if (tableElement.Name.LocalName.StartsWith("c_")) tables.Add(tableElement.Name.LocalName.Substring(2));
                            else tables.Add(tableElement.Name.LocalName);
                        }
                        break;
                    case "attributes":
                        foreach (XElement attrElement in xe.Elements())
                            attributes.Add(attrElement.Name.LocalName);
                        break;
                    case "values":
                        foreach (XElement valueElement in xe.Elements())
                            values.Add(valueElement.Name.LocalName);
                        break;
                    default:
                        throw new Exception("Pivot.FromXElement: Unexpected elements found in XML");
                }
            }
            return new Pivot(tables, attributes, values);
        }





    }
}
